package benchmarks.tpch.mysql.queries;

import api.result.Result;
import api.connection.DBConnection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Q15MySQL extends GenericQueryMySQL {
    public static final Boolean lock = Boolean.TRUE;
    public Q15MySQL() {
        super.queryName = queryName;
        super.query = query;
    }

//    public Result run(DBConnection connection) {
//        synchronized (lock) {
//            try {
//                Connection conn = (Connection) connection.makeConnection();
//                Statement statement = conn.createStatement();
//                try {
//                    statement.executeUpdate(query_createView);
//                    ResultSet rs = statement.executeQuery(query);
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                } finally {
//                    statement.executeUpdate(query_dropView);
//                }
//                conn.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        System.out.println(queryName + " ok");
//        return null;
//    }

    public static final String queryName = "Q15";

//    public static final String query_createView =
//            "create view revenue0 (supplier_no, total_revenue) as\n" +
//            "\tselect\n" +
//            "\t\tl_suppkey,\n" +
//            "\t\tsum(l_extendedprice * (1 - l_discount))\n" +
//            "\tfrom\n" +
//            "\t\tlineitem\n" +
//            "\twhere\n" +
//            "\t\tl_shipdate >= date '1996-01-01'\n" +
//            "\t\tand l_shipdate < date '1996-01-01' + interval '3' month\n" +
//            "\tgroup by\n" +
//            "\t\tl_suppkey";

    public static final String query =
            "select\n" +
            "\ts_suppkey,\n" +
            "\ts_name,\n" +
            "\ts_address,\n" +
            "\ts_phone,\n" +
            "\ttotal_revenue\n" +
            "from\n" +
            "\tsupplier,\n" +
            "\t(select\n" +
                    "\t\tl_suppkey,\n" +
                    "\t\tsum(l_extendedprice * (1 - l_discount)) as total_revenue,\n" +
                    "\t\tmax(l_extendedprice * (1 - l_discount)) as max_revenue\n" +
                    "\tfrom\n" +
                    "\t\tlineitem\n" +
                    "\twhere\n" +
                    "\t\tl_shipdate >= date '1996-01-01'\n" +
                    "\t\tand l_shipdate < date '1996-01-01' + interval '3' month\n" +
                    "\tgroup by\n" +
                    "\t\tl_suppkey)\n" +
                    "\tas revenue\n" +
            "where\n" +
            "\ts_suppkey = l_suppkey\n" +
            "\tand total_revenue = max_revenue\n" +
            "order by\n" +
            "\ts_suppkey";

//    public static final String query_dropView = "drop view revenue0";

//    public static final String query =
//            "select\n" +
//                    "\ts_suppkey,\n" +
//                    "\ts_name,\n" +
//                    "\ts_address,\n" +
//                    "\ts_phone,\n" +
//                    "\ttotal_revenue\n" +
//                    "from\n" +
//                    "\tsupplier,\n" +
//                    "\trevenue0\n" +
//                    "where\n" +
//                    "\ts_suppkey = supplier_no\n" +
//                    "\tand total_revenue = (\n" +
//                    "\t\tselect\n" +
//                    "\t\t\tmax(total_revenue)\n" +
//                    "\t\tfrom\n" +
//                    "\t\t\trevenue0\n" +
//                    "\t)\n" +
//                    "order by\n" +
//                    "\ts_suppkey";
}
